package com.project.movie.dao;

import com.project.movie.dto.ActorRole;
import com.project.movie.entities.Roles;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface RoleRepository extends CrudRepository<Roles,Long> {
    @Override
    List<Roles> findAll();
    @Query(nativeQuery = true,value = " SELECT r.name, r.relation , COUNT(cr.roles_id) AS rolesActorCount " +
            " FROM  roles AS r LEFT JOIN actor_roles AS cr ON cr.roles_id = r.roles_id " +
            " GROUP BY r.name, r.relation ORDER BY 3 desc; ")
    List<ActorRole> actorRole();
}
